package com.mida.dms.knw.util;


import com.mida.dms.knw.util.vo.ColumnVo;

import java.util.List;

/**
 * @ClassName :  DataTableCreateUtil
 * @Description :
 */
public class DataTableCreateUtil {

    /**
     * Description : 生成MySql的建表语句
     *
     * @param tableName    表名称
     * @param tableRemarks 表注释
     * @param columnVos    字段信息
     * @return : java.lang.String
     * @throws Exception
     */
    public static String getMySqlCreate(String tableName, String tableRemarks, List<ColumnVo> columnVos) throws Exception {
        try {
            StringBuffer sqlBuffer = new StringBuffer();
            sqlBuffer.append("create table " + tableName + " (");
            for (int i = 0; i < columnVos.size(); i++) {
                ColumnVo columnVo = columnVos.get(i);
                String columnName = columnVo.getColumnName();
                String columnType = columnVo.getColumnType();
                Integer columnSize = columnVo.getColumnSize();
                Integer columnDecimalSize = columnVo.getColumnDecimalSize();
                if ("整数".equals(columnType)) {
                    sqlBuffer.append(columnName + " int " + "(" + columnSize + ") ");
                } else if ("小数".equals(columnType)) {
                    sqlBuffer.append(columnName + " double " + "(" + columnSize + "," + columnDecimalSize + ")");
                } else if ("字符串".equals(columnType)) {
                    sqlBuffer.append(columnName + " varchar " + "(" + columnSize + ") ");
                } else if ("时间".equals(columnType)) {
                    sqlBuffer.append(columnName + " datetime ");
                } else if ("二进制".equals(columnType)) {
                    sqlBuffer.append(columnName + " text ");
                } else if ("布尔型".equals(columnType)) {
                    sqlBuffer.append(columnName + " tinyint(1) ");
                }
                Boolean isPrimary = columnVo.getPrimary();
                String primary = "";
                if (isPrimary != null && isPrimary) {
                    primary = " primary key ";
                }
                Boolean isNullable = columnVo.getNullable();
                String nullable = "";
                if (isNullable != null && !isNullable) {
                    nullable = " not null ";
                }
                String remarks = columnVo.getRemarks();
                sqlBuffer.append(primary + nullable + " comment '" + remarks + "',");
            }
            String version = columnVos.get(0).getVersion();
            String s = sqlBuffer.toString();
            String sql = s.substring(0, s.length() - 1) + ") COMMENT='" + tableRemarks + "_" + version +"'";
            return sql;
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("生成MySql的建表语句出现异常！");
        }
    }


     /**
     * Description : 生成SqlServer的建表语句
     *
     * @param tableName    表名称
     * @param tableRemarks 表注释
     * @param columnVos    字段信息
     * @return : java.lang.String
     * @throws Exception
     */
     public static String getSqlServerCreate(String tableName, String tableRemarks, List<ColumnVo> columnVos) {
        StringBuffer sqlBuffer = new StringBuffer();
        sqlBuffer.append("create table " + tableName + " (");
        for (int i = 0; i < columnVos.size(); i++) {
            ColumnVo columnVo = columnVos.get(i);
            String columnName = columnVo.getColumnName();
            String columnType = columnVo.getColumnType();
            Integer columnSize = columnVo.getColumnSize();
            Integer columnDecimalSize = columnVo.getColumnDecimalSize();
            String type = "";
            if ("整数".equals(columnType)) {
                type = "numeric";
                sqlBuffer.append(columnName + " " + type + "(" + columnSize + ") ");
            } else if ("小数".equals(columnType)) {
                type = "decimal";
                sqlBuffer.append(columnName + " " + type + "(" + columnSize + "," + columnDecimalSize + ")");
            } else if ("时间".equals(columnType)) {
                type = "datetime";
                sqlBuffer.append(columnName + " " + type + " ");
            } else if ("字符串".equals(columnType)) {
                type = "varchar";
                sqlBuffer.append(columnName + " " + type + "(" + columnSize + ") ");
            } else if ("二进制".equals(columnType)) {
                type = "varchar(MAX)";
                sqlBuffer.append(columnName + " " + type + " ");
            } else if ("布尔型".equals(columnType)) {
                type = "bit";
                sqlBuffer.append(columnName + " " + type + " ");
            }

            Boolean isPrimary = columnVo.getPrimary();
            String primary = "";
            if (isPrimary != null && isPrimary) {
                primary = " primary key ";
            }
            Boolean isNullable = columnVo.getNullable();
            String nullable = "";
            if (isNullable != null && !isNullable) {
                nullable = " not null ";
            }
            sqlBuffer.append(primary + nullable + ",");
        }
        String s = sqlBuffer.toString();
        String sql = s.substring(0, s.length() - 1) + ")";
        StringBuffer sqlBuffer1 = new StringBuffer();
        for (int i = 0; i < columnVos.size(); i++) {
            ColumnVo columnVo = columnVos.get(i);
            String columnName = columnVo.getColumnName();
            String remarks = columnVo.getRemarks();
            sqlBuffer1.append("exec sp_addextendedproperty N'MS_Description', N'" + remarks + "', N'user', N'dbo', N'table', N'" + tableName + "', N'column', N'" + columnName + "' " + "\n" );
        }

        String version = columnVos.get(0).getVersion();
        sqlBuffer1.append("exec sp_addextendedproperty N'MS_Description', N'" + tableRemarks +"_"+ version + "', N'user', N'dbo', N'table', N'" + tableName + "', null, null" + "\n" );
        String s1 = sql.concat(sqlBuffer1.toString());
        return s1;
    }


    /**
     * Description : 生成Oracle的建表语句
     *
     * @param tableName    表名称
     * @param tableRemarks 表注释
     * @param columnVos    字段信息
     * @return : java.lang.String
     * @throws Exception
     */
    public static String getOracleCreate(String tableName, String tableRemarks, List<ColumnVo> columnVos) {
        StringBuffer sqlBuffer = new StringBuffer();
        sqlBuffer.append("create table " + tableName + " (");
        for (int i = 0; i < columnVos.size(); i++) {
            ColumnVo columnVo = columnVos.get(i);
            String columnName = columnVo.getColumnName();
            String columnType = columnVo.getColumnType();
            Integer columnSize = columnVo.getColumnSize();
            Integer columnDecimalSize = columnVo.getColumnDecimalSize();
            String type = "";
            if ("整数".equals(columnType)) {
                type = "NUMBER";
                sqlBuffer.append(columnName + " " + type + "(" + columnSize + ") ");
            } else if ("小数".equals(columnType)) {
                type = "NUMBER";
                sqlBuffer.append(columnName + " " + type + "(" + columnSize + "," + columnDecimalSize + ")");
            } else if ("字符串".equals(columnType)) {
                type = "VARCHAR2";
                sqlBuffer.append(columnName + " " + type + "(" + columnSize + ") ");
            } else if ("时间".equals(columnType)) {
                type = "TIMESTAMP(6)";
                sqlBuffer.append(columnName + " " + type + " ");
            } else if ("二进制".equals(columnType)) {
                type = "VARCHAR2(4000)";
                sqlBuffer.append(columnName + " " + type + " ");
            } else if ("布尔型".equals(columnType)) {
                type = "number(1)";
                sqlBuffer.append(columnName + " " + type + " ");
            }
            Boolean isPrimary = columnVo.getPrimary();
            String primary = "";
            if (isPrimary != null && isPrimary) {
                primary = " primary key ";
            }
            Boolean isNullable = columnVo.getNullable();
            String nullable = "";
            if (isNullable != null && !isNullable) {
                nullable = " not null ";
            }
            sqlBuffer.append(primary + nullable + ",");
        }
        String s = sqlBuffer.toString();
        String sql = s.substring(0, s.length() - 1) + ");";
        StringBuffer sqlBuffer1 = new StringBuffer();
        for (int i = 0; i < columnVos.size(); i++) {
            ColumnVo columnVo = columnVos.get(i);
            String columnName = columnVo.getColumnName();
            String remarks = columnVo.getRemarks();
            sqlBuffer1.append("comment on column " + tableName + "." + columnName + " is '" + remarks + "';");
        }
        String version = columnVos.get(0).getVersion();
        sqlBuffer1.append("comment on table " + tableName + " is '" + tableRemarks + "_" + version +"';");
        String s1 = sql.concat(sqlBuffer1.toString());
        return s1;
    }




    /**
     * Description : 生成MySql的建表语句
     *
     * @param tableName    表名称
     * @param tableRemarks 表注释
     * @param columnVos    字段信息
     * @return : java.lang.String
     * @throws Exception
     */
    public static String getMySqlCreateString(String tableName, String tableRemarks, List<ColumnVo> columnVos) throws Exception {
        try {
            StringBuffer sqlBuffer = new StringBuffer();
            sqlBuffer.append("create table " + tableName + " (");
            for (int i = 0; i < columnVos.size(); i++) {
                ColumnVo columnVo = columnVos.get(i);
                String columnName = columnVo.getColumnName();
                String columnType = columnVo.getColumnType();
                Integer columnSize = columnVo.getColumnSize();
                Integer columnDecimalSize = columnVo.getColumnDecimalSize();
                if ("整数".equals(columnType.toUpperCase())) {
                    sqlBuffer.append(columnName + " int " + "(" + columnSize + ") ");
                } else if ("小数".equals(columnType.toUpperCase())) {
                    sqlBuffer.append(columnName + " double " + "(" + columnSize + "," + columnDecimalSize + ")");
                } else if ("字符串".equals(columnType.toUpperCase())) {
                    sqlBuffer.append(columnName + " varchar " + "(" + columnSize + ") ");
                } else if ("时间".equals(columnType.toUpperCase())) {
                    sqlBuffer.append(columnName + " datetime ");
                } else if ("二进制".equals(columnType)) {
                    sqlBuffer.append(columnName + " text ");
                } else if ("布尔型".equals(columnType)) {
                    sqlBuffer.append(columnName + " tinyint(1) ");
                }

                Boolean isPrimary = columnVo.getPrimary();
                String primary = "";
                if (isPrimary != null && isPrimary) {
                    primary = " primary key ";
                }
                Boolean isNullable = columnVo.getNullable();
                String nullable = "";
                if (isNullable != null && !isNullable) {
                    nullable = " not null ";
                }
                String remarks = columnVo.getRemarks();
                sqlBuffer.append(primary + nullable + " comment '" + remarks + "'," + "<br/>");
            }
            String version = columnVos.get(0).getVersion();
            String s = sqlBuffer.toString();
            String sql = s.substring(0, s.length() - 6) + ") COMMENT='" + tableRemarks + "_" + version +"'";
            return sql;
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("生成MySql的建表语句出现异常！");
        }
    }


    /**
     * Description : 生成SqlServer的建表语句
     *
     * @param tableName    表名称
     * @param tableRemarks 表注释
     * @param columnVos    字段信息
     * @return : java.lang.String
     * @throws Exception
     */
    public static String getSqlServerCreateString(String tableName, String tableRemarks, List<ColumnVo> columnVos) {
        StringBuffer sqlBuffer = new StringBuffer();
        sqlBuffer.append("create table " + tableName + " (");
        for (int i = 0; i < columnVos.size(); i++) {
            ColumnVo columnVo = columnVos.get(i);
            String columnName = columnVo.getColumnName();
            String columnType = columnVo.getColumnType();
            Integer columnSize = columnVo.getColumnSize();
            Integer columnDecimalSize = columnVo.getColumnDecimalSize();
            String type = "";
            if ("整数".equals(columnType)) {
                type = "numeric";
                sqlBuffer.append(columnName + " " + type + "(" + columnSize + ") ");
            } else if ("小数".equals(columnType)) {
                type = "decimal";
                sqlBuffer.append(columnName + " " + type + "(" + columnSize + "," + columnDecimalSize + ")");
            } else if ("时间".equals(columnType)) {
                type = "datetime";
                sqlBuffer.append(columnName + " " + type + " ");
            } else if ("字符串".equals(columnType)) {
                type = "varchar";
                sqlBuffer.append(columnName + " " + type + "(" + columnSize + ") ");
            } else if ("二进制".equals(columnType)) {
                type = "varchar(MAX)";
                sqlBuffer.append(columnName + " " + type + " ");
            } else if ("布尔型".equals(columnType)) {
                type = "bit";
                sqlBuffer.append(columnName + " " + type + " ");
            }
            Boolean isPrimary = columnVo.getPrimary();
            String primary = "";
            if (isPrimary != null && isPrimary) {
                primary = " primary key ";
            }
            Boolean isNullable = columnVo.getNullable();
            String nullable = "";
            if (isNullable != null && !isNullable) {
                nullable = " not null ";
            }
            sqlBuffer.append(primary + nullable + "," + "<br/>");
        }
        String s = sqlBuffer.toString();
        String sql = s.substring(0, s.length() - 6) + ")"  + "<br/>";
        StringBuffer sqlBuffer1 = new StringBuffer();
        for (int i = 0; i < columnVos.size(); i++) {
            ColumnVo columnVo = columnVos.get(i);
            String columnName = columnVo.getColumnName();
            String remarks = columnVo.getRemarks();
            sqlBuffer1.append("exec sp_addextendedproperty N'MS_Description', N'" + remarks + "', N'user', N'dbo', N'table', N'" + tableName + "', N'column', N'" + columnName + "' " + "<br/>" );
        }

        String version = columnVos.get(0).getVersion();
        sqlBuffer1.append("exec sp_addextendedproperty N'MS_Description', N'" + tableRemarks +"_"+ version + "', N'user', N'dbo', N'table', N'" + tableName + "', null, null" + "<br/>" );
        String s1 = sql.concat(sqlBuffer1.toString());
        return s1;
    }


    /**
     * Description : 生成Oracle的建表语句
     *
     * @param tableName    表名称
     * @param tableRemarks 表注释
     * @param columnVos    字段信息
     * @return : java.lang.String
     * @throws Exception
     */
    public static String getOracleCreateString(String tableName, String tableRemarks, List<ColumnVo> columnVos) {
        StringBuffer sqlBuffer = new StringBuffer();
        sqlBuffer.append("create table " + tableName + " (");
        for (int i = 0; i < columnVos.size(); i++) {
            ColumnVo columnVo = columnVos.get(i);
            String columnName = columnVo.getColumnName();
            String columnType = columnVo.getColumnType();
            Integer columnSize = columnVo.getColumnSize();
            Integer columnDecimalSize = columnVo.getColumnDecimalSize();
            String type = "";
            if ("整数".equals(columnType)) {
                type = "NUMBER";
                sqlBuffer.append(columnName + " " + type + "(" + columnSize + ") ");
            } else if ("小数".equals(columnType)) {
                type = "NUMBER";
                sqlBuffer.append(columnName + " " + type + "(" + columnSize + "," + columnDecimalSize + ")");
            } else if ("字符串".equals(columnType)) {
                type = "VARCHAR2";
                sqlBuffer.append(columnName + " " + type + "(" + columnSize + ") ");
            } else if ("时间".equals(columnType)) {
                type = "TIMESTAMP(6)";
                sqlBuffer.append(columnName + " " + type + " ");
            } else if ("二进制".equals(columnType)) {
                type = "VARCHAR2(4000)";
                sqlBuffer.append(columnName + " " + type + " ");
            } else if ("布尔型".equals(columnType)) {
                type = "number(1)";
                sqlBuffer.append(columnName + " " + type + " ");
            }
            Boolean isPrimary = columnVo.getPrimary();
            String primary = "";
            if (isPrimary != null && isPrimary) {
                primary = " primary key ";
            }
            Boolean isNullable = columnVo.getNullable();
            String nullable = "";
            if (isNullable != null && !isNullable) {
                nullable = " not null ";
            }
            sqlBuffer.append(primary + nullable + "," + "<br/>");
        }
        String s = sqlBuffer.toString();
        String sql = s.substring(0, s.length() - 6) + ");" + "<br/>";
        StringBuffer sqlBuffer1 = new StringBuffer();
        for (int i = 0; i < columnVos.size(); i++) {
            ColumnVo columnVo = columnVos.get(i);
            String columnName = columnVo.getColumnName();
            String remarks = columnVo.getRemarks();
            sqlBuffer1.append("comment on column " + tableName + "." + columnName + " is '" + remarks + "';" + "<br/>");
        }
        String version = columnVos.get(0).getVersion();
        sqlBuffer1.append("comment on table " + tableName + " is '" + tableRemarks + "_" + version +"';");
        String s1 = sql.concat(sqlBuffer1.toString());
        return s1;
    }
 
 
    /**
     * Description : 生成Hive的建表语句
     *
     * @param tableName    表名称
     * @param tableRemarks 表注释
     * @param columnVos    字段信息
     * @return : java.lang.String
     * @throws Exception
     */
    public String getHiveCreate(String tableName, String tableRemarks, List<ColumnVo> columnVos) throws Exception {
        try {
            StringBuffer sqlBuffer = new StringBuffer();
            sqlBuffer.append("create table " + tableName + " (");
            for (int i = 0; i < columnVos.size(); i++) {
                ColumnVo columnVo = columnVos.get(i);
                String columnName = columnVo.getColumnName();
                String columnType = columnVo.getColumnType();
                Integer columnSize = columnVo.getColumnSize();
                if ("INT".equals(columnType.toUpperCase())) {
                    sqlBuffer.append(columnName + " int ");
                } else if ("DOUBLE".equals(columnType.toUpperCase())) {
                    sqlBuffer.append(columnName + " double ");
                } else if ("VARCHAR".equals(columnType.toUpperCase())) {
                    sqlBuffer.append(columnName + " varchar " + "(" + columnSize + ") ");
                } else if ("DATETIME".equals(columnType.toUpperCase())) {
                    sqlBuffer.append(columnName + " datetime ");
                } else if ("TIMESTAMP".equals(columnType.toUpperCase())) {
                    sqlBuffer.append(columnName + " timestamp ");
                }
                String remarks = columnVo.getRemarks();
                sqlBuffer.append(" comment '" + remarks + "',");
            }
            String s = sqlBuffer.toString();
            String sql = s.substring(0, s.length() - 1) + ") COMMENT '" + tableRemarks + "'";
            return sql;
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("生成hive的建表语句出现异常！");
        }
    }
 
 
    /**
     * Description : 生成Hbase的建表语句
     *
     * @param tableName    表名称
     * @param tableRemarks 表注释
     * @param columnVos    字段信息
     * @return : java.lang.String
     * @throws Exception
     */
    public String getHbaseCreate(String tableName, String tableRemarks, List<ColumnVo> columnVos) throws Exception {
        try {
            StringBuffer sqlBuffer = new StringBuffer();
            sqlBuffer.append("create table " + tableName + " (");
            for (int i = 0; i < columnVos.size(); i++) {
                ColumnVo columnVo = columnVos.get(i);
                String columnName = columnVo.getColumnName();
                String columnType = columnVo.getColumnType();
                Integer columnSize = columnVo.getColumnSize();
                if ("INT".equals(columnType.toUpperCase())) {
                    sqlBuffer.append(columnName + " BIGINT  ");
                } else if ("CHAR".equals(columnType.toUpperCase())) {
                    sqlBuffer.append(columnName + " char " + "(" + columnSize + ") ");
                } else if ("DATE".equals(columnType.toUpperCase())) {
                    sqlBuffer.append(columnName + " date ");
                }
                Boolean isNullable = columnVo.getNullable();
                String nullable = "";
                if (isNullable != null && !isNullable) {
                    nullable = " not null ";
                }
                Boolean isPrimary = columnVo.getPrimary();
                String primary = "";
                if (isPrimary != null && isPrimary) {
                    primary = " primary key ";
                }
                String remarks = columnVo.getRemarks();
                sqlBuffer.append(nullable + primary + " ,");
            }
            String s = sqlBuffer.toString();
            String sql = s.substring(0, s.length() - 1) + ")  ";
            return sql;
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("生成hbase的建表语句出现异常！");
        }
    }
 
 
}